﻿using System;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

namespace Shuba.Data
{
    public class Holiday
    {
        public Holiday()
        { 
        }

        public List<Shuba.Model.HolidayInfo> GetList(string HOLIDAY_NAME)
        {
            List<Shuba.Model.HolidayInfo> _list = new List<Shuba.Model.HolidayInfo>();
            Database mydb = DBConnection.CreateDatabase();

            string mysql = String.Format(@"SELECT [HOLIDAY_DATE],[HOLIDAY_NAME],[ENTRY_BY]
                                            ,[ENTRY_DATE],[UPDATE_BY],[UPDATE_DATE],[IS_ACTIVE]
                                           FROM [TBL_MS_HOLIDAY] WHERE HOLIDAY_NAME LIKE '%{0}%' ", HOLIDAY_NAME);
            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);

            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    _list.Add(new Shuba.Model.HolidayInfo(mydr.GetDateTime(0), mydr.GetString(1), mydr.GetString(2), mydr.GetDateTime(3), mydr.GetString(4), mydr.GetDateTime(5), mydr.GetString(6)));
                }
                mydr.Close();
            }
            return _list;
        }

        public Shuba.Model.HolidayInfo Get(DateTime HOLIDAY_DATE)
        {
            Shuba.Model.HolidayInfo _list = new Shuba.Model.HolidayInfo();
            Database mydb = DBConnection.CreateDatabase();

            string mysql = @"SELECT [HOLIDAY_DATE],[HOLIDAY_NAME],[ENTRY_BY]
                                ,[ENTRY_DATE],[UPDATE_BY],[UPDATE_DATE],[IS_ACTIVE]
                             FROM [TBL_MS_HOLIDAY] WHERE YEAR(HOLIDAY_DATE) = '" + HOLIDAY_DATE.Year + "' " +
                                    " AND MONTH(HOLIDAY_DATE) = '" + HOLIDAY_DATE.Month + "' " +
                                    " AND DAY(HOLIDAY_DATE) = '" + HOLIDAY_DATE.Day + "'";
            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);

            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    _list = new Shuba.Model.HolidayInfo(mydr.GetDateTime(0), mydr.GetString(1), mydr.GetString(2), mydr.GetDateTime(3), mydr.GetString(4), mydr.GetDateTime(5), mydr.GetString(6));
                }
                mydr.Close();
            }
            return _list;
        }

        public static bool CheckExistingCodeAndName(DateTime HOLIDAY_DATE, string HOLIDAY_NAME)
        {
            bool status = false;
            Database mydb = DBConnection.CreateDatabase();

            string mysql = @"SELECT [HOLIDAY_DATE],[HOLIDAY_NAME],[ENTRY_BY]
                                ,[ENTRY_DATE],[UPDATE_BY],[UPDATE_DATE],[IS_ACTIVE]
                             FROM [TBL_MS_HOLIDAY] WHERE YEAR(HOLIDAY_DATE) = '" + HOLIDAY_DATE.Year + "' " +
                                " AND MONTH(HOLIDAY_DATE) = '" + HOLIDAY_DATE.Month + "' " +
                                " AND DAY(HOLIDAY_DATE) = '" + HOLIDAY_DATE.Day + "' OR LTRIM(RTRIM(CHANNEL_NAME)) LIKE '" + HOLIDAY_NAME.Trim() + "'";
            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);

            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    status = true;
                }
                mydr.Close();
            }
            return status;
        }

        public int Save(Shuba.Model.HolidayInfo Info)
        {
            int status = 0;
            try
            {
                Database mydb = DBConnection.CreateDatabase();

                DbCommand mycomm = mydb.GetSqlStringCommand(@"
                       	    UPDATE TBL_MS_HOLIDAY_TMP SET 
                                HOLIDAY_NAME = @HOLIDAY_NAME,
                                ACT_TYPE = @ACT_TYPE,
                                ENTRY_BY = @ENTRY_BY,
                                ENTRY_DATE = @ENTRY_DATE,
                                UPDATE_BY = @UPDATE_BY,
                                UPDATE_DATE = @UPDATE_DATE,
                                IS_ACTIVE = @IS_ACTIVE,
                                HOLIDAY_DATE_ORIG = @HOLIDAY_DATE_ORIG
                                WHERE YEAR(HOLIDAY_DATE)=@YEAR AND MONTH(HOLIDAY_DATE)=@MONTH AND DAY(HOLIDAY_DATE)=@DAY;
            	            IF @@ROWCOUNT = 0
                            INSERT INTO TBL_MS_HOLIDAY_TMP(HOLIDAY_DATE,HOLIDAY_NAME,ACT_TYPE,ENTRY_BY,ENTRY_DATE,UPDATE_BY,UPDATE_DATE,IS_ACTIVE,HOLIDAY_DATE_ORIG) 
                            VALUES (@HOLIDAY_DATE,@HOLIDAY_NAME,@ACT_TYPE,@ENTRY_BY,@ENTRY_DATE,@UPDATE_BY,@UPDATE_DATE,@IS_ACTIVE,@HOLIDAY_DATE_ORIG)");

                mydb.AddInParameter(mycomm, "@HOLIDAY_DATE", DbType.DateTime, Info.HOLIDAY_DATE);
                mydb.AddInParameter(mycomm, "@YEAR", DbType.Int32, Info.HOLIDAY_DATE.Year);
                mydb.AddInParameter(mycomm, "@MONTH", DbType.Int32, Info.HOLIDAY_DATE.Month);
                mydb.AddInParameter(mycomm, "@DAY", DbType.Int32, Info.HOLIDAY_DATE.Day);
                mydb.AddInParameter(mycomm, "@HOLIDAY_NAME", DbType.String, Info.HOLIDAY_NAME.ToUpper());
                mydb.AddInParameter(mycomm, "@ACT_TYPE", DbType.String, Info.ACT_TYPE);
                mydb.AddInParameter(mycomm, "@ENTRY_BY", DbType.String, Info.ENTRY_BY);
                mydb.AddInParameter(mycomm, "@ENTRY_DATE", DbType.DateTime, Info.ENTRY_DATE);
                mydb.AddInParameter(mycomm, "@UPDATE_BY", DbType.String, Info.UPDATE_BY);
                mydb.AddInParameter(mycomm, "@UPDATE_DATE", DbType.DateTime, Info.UPDATE_DATE);
                mydb.AddInParameter(mycomm, "@IS_ACTIVE", DbType.String, Info.IS_ACTIVE);
                mydb.AddInParameter(mycomm, "@HOLIDAY_DATE_ORIG", DbType.DateTime, Info.HOLIDAY_DATE_ORIG);

                status = mydb.ExecuteNonQuery(mycomm);
            }
            catch (Exception ex) { }
            return status;
        }

        public List<Shuba.Model.HolidayInfo> GetListApproval(string UPDATE_BY)
        {
            List<Shuba.Model.HolidayInfo> _info = new List<Shuba.Model.HolidayInfo>();
            Database mydb = DBConnection.CreateDatabase();

            string mysql = String.Format(@"SELECT [HOLIDAY_DATE],[HOLIDAY_NAME]
                                                  ,[ENTRY_BY],[ENTRY_DATE],[UPDATE_BY]
                                                  ,[UPDATE_DATE],[IS_ACTIVE]
                                                  ,[ACT_TYPE],[HOLIDAY_DATE_ORIG]
                                           FROM [TBL_MS_HOLIDAY_TMP] 
                                           WHERE (RTRIM(UPDATE_BY) <> '{0}') ORDER BY HOLIDAY_DATE ", UPDATE_BY);
            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);

            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    _info.Add(new Shuba.Model.HolidayInfo(mydr.GetDateTime(0), mydr.GetString(1), mydr.GetString(2), mydr.GetDateTime(3), mydr.GetString(4), mydr.GetDateTime(5), mydr.GetString(6), mydr.GetString(7), mydr.GetDateTime(8)));
                }
                mydr.Close();
            }
            return _info;
        }

        public List<DateTime> GetHoliday()
        {
            List<DateTime> holiday = new List<DateTime>();
            try
            {
                Database db = DBConnection.CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(@"SELECT HOLIDAY_DATE FROM TBL_MS_HOLIDAY WHERE YEAR(HOLIDAY_DATE) = YEAR(GETDATE()) ORDER BY HOLIDAY_DATE");
                DataSet dt = db.ExecuteDataSet(cmd);
                foreach (DataRow item in dt.Tables[0].Rows)
                {
                    holiday.Add(Convert.ToDateTime(item[0]));
                }
            }
            catch (Exception) { }
            return holiday;
        }

        public void Approve(DateTime HOLIDAY_DATE, string ACT_TYPE)
        {
            Database mydb = DBConnection.CreateDatabase();
            DbCommand mycomm = mydb.GetStoredProcCommand("APPROVAL_HOLIDAY");
            mydb.AddInParameter(mycomm, "@HOLIDAY_DATE", DbType.DateTime, HOLIDAY_DATE);
            mydb.AddInParameter(mycomm, "@ACT_TYPE", DbType.String, ACT_TYPE);

            mydb.ExecuteNonQuery(mycomm);
        }

        public void Reject(DateTime HOLIDAY_DATE)
        {
            Database mydb = DBConnection.CreateDatabase();

            string mysql = @"DELETE FROM TBL_MS_HOLIDAY_TMP 
                             WHERE YEAR(HOLIDAY_DATE)='" + HOLIDAY_DATE.Year + "' " +
                                   " AND MONTH(HOLIDAY_DATE)='" + HOLIDAY_DATE.Month + "' " +
                                   " AND DAY(HOLIDAY_DATE)='" + HOLIDAY_DATE.Day + "'";
            DbCommand mycmd = mydb.GetSqlStringCommand(mysql);

            mydb.ExecuteNonQuery(mycmd);
        }

        public Shuba.Model.HolidayInfo GetApprovalByID(DateTime HOLIDAY_DATE)
        {
            Shuba.Model.HolidayInfo _list = new Shuba.Model.HolidayInfo();
            Database mydb = DBConnection.CreateDatabase();

            string mysql = @"SELECT [HOLIDAY_DATE],[HOLIDAY_NAME]
                                  ,[ENTRY_BY],[ENTRY_DATE],[UPDATE_BY]
                                  ,[UPDATE_DATE],[IS_ACTIVE]
                                  ,[ACT_TYPE],[HOLIDAY_DATE_ORIG]
                            FROM TBL_MS_HOLIDAY_TMP " +
                                   " WHERE YEAR(HOLIDAY_DATE)='" + HOLIDAY_DATE.Year + "' " +
                                   " AND MONTH(HOLIDAY_DATE)='" + HOLIDAY_DATE.Month + "' " +
                                   " AND DAY(HOLIDAY_DATE)='" + HOLIDAY_DATE.Day + "'";
            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);

            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    _list = new Shuba.Model.HolidayInfo(mydr.GetDateTime(0), mydr.GetString(1), mydr.GetString(2), mydr.GetDateTime(3), mydr.GetString(4), mydr.GetDateTime(5), mydr.GetString(6), mydr.GetString(7), mydr.GetDateTime(8));
                }
                mydr.Close();
            }
            return _list;
        }
    }
}
